package com.ecsolutions.dao;

import com.ecsolutions.dao.SqlProvider.repaymentScheModify_Provider;
import com.ecsolutions.entity.repaymentScheModify_entity;
import com.ecsolutions.entity.repaymentSchedule_entity;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by ecs on 2017/9/4.
 */
@Repository
public interface repaymentScheModify_DAO {

    @Select("SELECT * FROM hkbinmas Where TRIM(refno) = TRIM(#{refno})")
    @ResultType(repaymentScheModify_entity.class)
    repaymentScheModify_entity getLoanDetails(String refno);

    //通过refno查询出还款计划表的信息
    @Select("select * from hkbinmf4 where TRIM(refno) = TRIM(#{refno})")
    @ResultType(repaymentSchedule_entity.class)
    List<repaymentSchedule_entity> getRepaymentSchedule(@Param("refno") String refno);

    @Select("SELECT Sum(instamt) as sum_instamt,Sum(prnamt) as sum_prnamt,Sum(intamt) as sum_intamt FROM hkbinmf4 WHERE refno =#{refno}")
    @ResultType(repaymentSchedule_entity.class)
    repaymentSchedule_entity getSumInfo(String refno);

    //通过refno查询出的repaymentScheModify_entity信息
    @Select("select * from hkbinmas where TRIM(refno) = TRIM(#{refno})")
    @ResultType(repaymentScheModify_entity.class)
    repaymentScheModify_entity getRepaymentSchedulePageByRefno(@Param("refno") String refno);

    @Select("SELECT intrate FROM hkbinmf4 Where TRIM(refno) = TRIM(#{refno}) and rownum = 1")
    @ResultType(String.class)
    String queryForIntrate(String refno);

    @Select("SELECT strdate FROM hkbinmf4 Where TRIM(refno) = TRIM(#{refno}) and rownum = 1")
    @ResultType(String.class)
    String queryForStrdate(String refno);

    @SelectProvider(type = repaymentScheModify_Provider.class, method = "getResultList")
    @ResultType(repaymentScheModify_entity.class)
    List<repaymentScheModify_entity> getResultList(@Param("refno")String refno);

    @SelectProvider(type = repaymentScheModify_Provider.class, method = "countResultList")
    @ResultType(Integer.class)
    Integer countResultList(@Param("refno")String refno);

    @Insert("insert into adsinmf4(refno,verno,instno,func,schetyp,subtype,custcod,strdate,duedate,intrate,ratetyp,spread,prntier,inttier,prnamt,intamt,instamt,lnosamt,lnosccy,appref,payfreq,ratebas) "
            +"values(#{refno,jdbcType=VARCHAR},#{verno,jdbcType=NUMERIC},#{instno,jdbcType=NUMERIC},#{func,jdbcType=VARCHAR},#{schetyp,jdbcType=VARCHAR},#{subtype,jdbcType=VARCHAR},#{custcod,jdbcType=VARCHAR},#{strdate,jdbcType=NUMERIC},#{duedate,jdbcType=NUMERIC},"
            +"#{intrate,jdbcType=NUMERIC},#{ratetyp,jdbcType=VARCHAR},#{spread,jdbcType=NUMERIC},#{prntier,jdbcType=NUMERIC},#{inttier,jdbcType=NUMERIC},#{prnamt,jdbcType=NUMERIC},"
            +"#{intamt,jdbcType=NUMERIC},#{instamt,jdbcType=NUMERIC},#{lnosamt,jdbcType=NUMERIC},#{lnosccy,jdbcType=VARCHAR},#{refno,jdbcType=VARCHAR},' ',' ')")
    void insertToAdsinmf4(repaymentSchedule_entity repaymentschedule_entity);

    @Insert("insert into hkbppmas(refno,idno,lclname,custcod,fliuser,flidate,auser,adate,verno,txno,func,flrecty,flrcsts,pending,fllcycd,custnam,custad1,custad2,custad3,loannat,applccy,applamt,appsts,apprccy,crbreq,apprlmt,loanref,schtyp,inpuser,autuser,appuser,crtdate,bankent,branch,tenor,loandbd,staffid,loanttl,sex,mstatus,natlity,birthdt,contact,income,expense,incprf,eplornm,eplora1,eplora2,eplora3,workyrs,busnat,ltdcomp,jappno,alwqprc,loctcod,idtype,crsdate,crbchkd,cscrver,crscore,emailad,retcorp,disbdat,numrevw,remark1,remark2,remark3,remark4,appendd,reguser,regdate,loantyp,txstat,flstscd,loanpur,appsudt,appstdt,updtime,advrefn,accoffr,prounit,flitime,atime,pcenter,acudbu,capppu,gstrsd,gstreg,gdsexp,lapppu,pjcode) "
            +"values(#{refno,jdbcType=VARCHAR},' ',' ',#{custcod,jdbcType=VARCHAR},#{userid,jdbcType=VARCHAR},(select to_char(sysdate,'yyyyMMdd') from dual),#{userid,jdbcType=VARCHAR},(select to_char(sysdate,'yyyyMMdd') from dual),1,0,'LORSYS',"
            +"#{fllcycd,jdbcType=VARCHAR},#{flrcsts,jdbcType=VARCHAR},'CREATION',#{fllcycd,jdbcType=VARCHAR},#{cusnm,jdbcType=VARCHAR},#{cusadd1,jdbcType=VARCHAR},"
            +"#{cusadd2,jdbcType=VARCHAR},#{cusadd3,jdbcType=VARCHAR},'S',#{lnccy,jdbcType=VARCHAR},#{lnamt,jdbcType=NUMERIC},'A',#{lnccy,jdbcType=VARCHAR},'N',#{lnamt,jdbcType=NUMERIC},#{refno,jdbcType=VARCHAR},"
            +"#{schetyp,jdbcType=VARCHAR},#{userid,jdbcType=VARCHAR},#{userid,jdbcType=VARCHAR},#{userid,jdbcType=VARCHAR},(select to_char(sysdate,'yyyyMMdd') from dual),#{bankent,jdbcType=VARCHAR},#{branch,jdbcType=VARCHAR},#{instno,jdbcType=VARCHAR},#{dwndate,jdbcType=VARCHAR},'N',' ',' ','0','00',0,' ',0,0,' ',' ',' ',' ',' ',0,' ',' ',0,' ',' ',' ',0,' ',0,0,' ',' ',0,0,' ',' ',' ',' ',(select to_char(sysdate,'yyyyMMdd') from dual),#{userid,jdbcType=VARCHAR},(select to_char(sysdate,'yyyyMMdd') from dual),'S',' ','S','1',(select to_char(sysdate,'yyyyMMdd') from dual),(select to_char(sysdate,'yyyyMMdd') from dual),0,' ',' ',' ',0,0,' ',' ',' ',' ',' ',' ',' ',' ')")
    void insertToHkbppmas(repaymentScheModify_entity repaymentschemodify_entity);

    @Insert("insert into hkbpptmp(refno,lineno,avaiamt,intrate,margin,feetype,feedate,feeamt,paymeth,ratetyp,repayda,repayam,ratetye,term,lineccy,schrate,intsped,fixflag,payfreq,payper,matdate) "
            +"values(#{arg1.refno,jdbcType=VARCHAR},' ',#{arg0.lnamt,jdbcType=NUMERIC},#{arg1.intrate,jdbcType=NUMERIC},#{arg0.hanchrg,jdbcType=NUMERIC},'F',to_number(#{arg1.duedate,jdbcType=VARCHAR}),0.00,'1',#{arg1.ratetyp,jdbcType=VARCHAR},to_number(#{arg0.fpaydat,jdbcType=VARCHAR}),0.00,"
            +"' ',' ',#{arg0.lnccy,jdbcType=VARCHAR},#{arg1.intrate,jdbcType=NUMERIC},#{arg1.spread,jdbcType=NUMERIC},' ',#{arg0.rpyfeq,jdbcType=NUMERIC},"
            +"' ',to_number(#{arg0.matdate,jdbcType=VARCHAR}))")
    void insertToHkbpptmp(repaymentScheModify_entity repaymentschemodify_entity,repaymentSchedule_entity repaymentschedule_entity);

    @Insert("insert into hkbindtl(refno,instno,verno,fliuser,flidate,auser,adate,func,branch,flstscd,crtdate,custcod,lnccy,lnosamt,grace,dlqdate,insstrd,insdued,instamt,insprn,insint,odint,odintac,latechg,lchgadj,intadj,odiadj,rpydate,payprn,payint,payodi,paylate,tintadj,todiadj,tlchadj,prounit,lapppu,odiacdt,lchacdt,prntier,inttier,schetyp,subtype,rpymth,rpyfeq,rpyper,inttype,ratetyp,spread,iratype,irafeq,iraper,nxtiadt,mthendi,comrate,comspre,highlow,odspre,overtyp,odratyp,perobr,triedn,loantyp) "
            +"values(#{arg0.refno,jdbcType=VARCHAR},#{arg0.curinno,jdbcType=VARCHAR},(SELECT NVL(MAX(verno),0)+1 FROM hkbindtl where refno = #{arg0.refno,jdbcType=VARCHAR}),#{arg0.userid,jdbcType=VARCHAR},(select to_char(sysdate,'yyyyMMdd') from dual),#{arg0.userid,jdbcType=VARCHAR},(select to_char(sysdate,'yyyyMMdd') from dual),#{arg1.func,jdbcType=VARCHAR},#{arg0.branch,jdbcType=VARCHAR},'A',(select to_char(sysdate,'yyyyMMdd') from dual),#{arg0.custcod,jdbcType=VARCHAR},#{arg0.lnccy,jdbcType=VARCHAR},#{arg0.lnosamt,jdbcType=VARCHAR},0,(select to_char(sysdate,'yyyyMMdd') from dual),#{arg1.strdate,jdbcType=VARCHAR},#{arg1.duedate,jdbcType=VARCHAR},#{arg1.instamt,jdbcType=VARCHAR},#{arg1.prnamt,jdbcType=VARCHAR},"
            +"#{arg1.intamt,jdbcType=VARCHAR},0,0,0,0,0,0,(select to_char(sysdate,'yyyyMMdd') from dual),0,0,0,0,0,0,0,' ',' ',0,0,#{arg1.prntier,jdbcType=VARCHAR},#{arg1.inttier,jdbcType=VARCHAR},#{arg0.schetyp,jdbcType=VARCHAR},#{arg0.subtype,jdbcType=VARCHAR},#{arg0.rpymth,jdbcType=NUMERIC},#{arg0.rpyfeq,jdbcType=NUMERIC},#{arg0.rpyper,jdbcType=VARCHAR},' ',"
            +"#{arg1.ratetyp,jdbcType=VARCHAR},#{arg1.spread,jdbcType=NUMERIC},'3',0,' ',0,' ',' ',0,' ',0,' ',' ',100,0,' ')")
    void insertToHkbindtl(repaymentScheModify_entity repaymentschemodify_entity,repaymentSchedule_entity repaymentschedule_entity);
}
